Re: [GENERAL] Emptying a database.

At 8:28 +0200 on 9/10/98, Karl Auer wrote:


> Or duplicate the database, delete everything from the duplicate (only need to
> do this once, or whenever you add/delete tables from your design), and simply
> copy the duplicate over the original whenever you want to clear it.

"Prometheus", the name of the mythological protector of mankind, means
"Forethought".

I always believe in creating a "database creation script" in advance, which
deletes the old tables, sequences, functions, and whatnot, and creates them
anew.

It overcomes the problem with pg_dump, which doesn't dump or restore
certain things like views etc, or which dumps things in an order which
doesn't work for everybody.

I also create a backup script which dumps the contents (only) of the
relevant tables, and a restore script which restores the contents.

In this way, when I have to change something in the structure of the
database, I add it to the "creation" script, dump the contents, run the
creation script, and restore them with the "restore" script, into the
modified database.

If the change involved a change in a table structure (added field, changed
its type, dropped a field), you have to change the dumped data before
restoring, which is easily done either by search-and-replace in you
favourite editor (worked for me in all cases until now), or using a perl
script. Since it is a consistent change most of the time (probably just
addind a "\t\\N" after some column), it's very easily done.

If the change was just a change of a function, a default value, a
constraint, an index, etc. - you are free of changing your data. Just dump,
re-create, and restore. Don't forget to dump and restore sequence values as
well.

Running such a "creation" script on its own is bound to leave you with
empty tables, all ready in the correct structure. I find this is the
preferable way to "flush" a database. I also find that when I'm done
developing an application, the resulting "creation" script will be the best
basis for the new production database.

The "creation" script also has the benefit of being full of comments which
explain what is hapenning, what the fields are, etc.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma